package controllers

import (
	"Number4/models"
	"database/sql"
	"encoding/json"
	"flag"
	"fmt"
	"github.com/astaxie/beego"
	_ "github.com/denisenkom/go-mssqldb"
	"log"
	"strconv"
	_ "strconv"
	"strings"
)


//设置连接变量
var debug = flag.Bool("debug", true, "enable debugging")
var password = flag.String("password", "root", "the password")
var port *int = flag.Int("port", 1433, "the port")
var server = flag.String("server", "127.0.0.1", "the server")
var user = flag.String("user", "sa", "the user")
var database = flag.String("database", "TrialOneTraining", "the name")
var firname=""
var secname=""

type MainController struct {
	beego.Controller
}


//创建study来实例用于接收
var s models.Study
//处理error
func check(err error){
	if(err!=nil){
		panic(err)
	}
}
//取得数据库连接实例
func GetDB() (*sql.DB, error) {
	connString := fmt.Sprintf("server=%s;database=%s;user id=%s;password=%s;port=%d;encrypt=disable", *server, *database, *user, *password, *port)
	db, err := sql.Open("mssql", connString)
	if err != nil {
		log.Fatal("Open connection failed:", err.Error())
		return nil, err
	}
	err = db.Ping()
	if err != nil {
		fmt.Print("PING:%s", err)
		return nil, err
	}
	return db, nil
}

func (c *MainController) Get() {
	c.TplName = "Login.html"
}
func (c *MainController) Login(){
	item := make(map[string]interface{})
	usname:=c.GetString("usname")
	pwd:=c.GetString("pwd")
	db, err := GetDB()
	rows, err := db.Query("select * from administrator where usname=? and pwd=?",usname,pwd)
	check(err)
	db.Close()
	columns, _ := rows.Columns()
	columnLength := len(columns)
	cache := make([]interface{}, columnLength) //临时存储每行数据
	for index, _ := range cache { //为每一列初始化一个指针
		var a interface{}
		cache[index] = &a
	}
	for rows.Next() {
		_ = rows.Scan(cache...)
		for i, data := range cache {
			item[columns[i]] = *data.(*interface{}) //取实际类型
		}
	}
	if(len(item)!=0){
		firname=usname
		c.Data["item"]=item
		c.GetStudy()
	}else {
		c.TplName="Login.html"
	}
}

//取得所有study
func (c *MainController) GetStudy(){
	fmt.Println("______GetStudy")
	db, err := GetDB()
	check(err)
	if err := c.ParseForm(&s); err != nil {
		//process err
	}
	rows, err := db.Query("SELECT StudyID,Name FROM dbo.Study order by StudyID")
	check(err)
	db.Close()
	//rows转map
	columns, _ := rows.Columns()
	columnLength := len(columns)
	cache := make([]interface{}, columnLength) //临时存储每行数据
	for index, _ := range cache { //为每一列初始化一个指针
		var a interface{}
		cache[index] = &a
	}
	var list []map[string]interface{} //返回的切片
	for rows.Next() {
		_ = rows.Scan(cache...)

		item := make(map[string]interface{})
		for i, data := range cache {
			item[columns[i]] = *data.(*interface{}) //取实际类型
		}
		list = append(list, item)
	}
	_ = rows.Close()
	c.Data["stu"]=list
	c.TplName="model.html"
}


//取得study的数据并放入Data
func (c *MainController) HandleSite() {
	fmt.Println("______HandleSite")
	db, err := GetDB()
	check(err)
	if err := c.ParseForm(&s); err != nil {
		//process err
	}
	rows, err := db.Query("SELECT SiteID,Name FROM dbo.Site order by SiteID")
	check(err)
	columns, err := rows.Columns()
	check(err)
	//装rows到json
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	if(list=="]"){
		list="[]"
	}

	//fmt.Println(list)
	c.Data["json"]=list
	c.ServeJSON()
}

func (c *MainController) HandleGroup() {
	fmt.Println("__________________HandleGroup")
	db, err := GetDB()
	check(err)
	if err := c.ParseForm(&s); err != nil {
		//process err
	}
	studyid, err := strconv.Atoi(c.GetString("studyid"))
	siteid, err := strconv.Atoi(c.GetString("siteid"))
	//debug fmt.Println("studyid:"+c.GetString("studyid")+"___siteid:"+c.GetString("siteid")
	rows, err := db.Query("SELECT GroupID,Name FROM [Group] where StudyID=? and SiteID=?",studyid,siteid)
	check(err)
	columns, err := rows.Columns()
	check(err)
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	if(list=="]"){
		list="[]"
	}

	//debug fmt.Println(list)
	c.Data["json"]=list
	c.ServeJSON()
}

func (c *MainController) HandleVisit() {
	fmt.Println("__________HandleVisit")
	db, err := GetDB()
	check(err)
	if err := c.ParseForm(&s); err != nil {
		//process err
	}
	groupid, err := strconv.Atoi(c.GetString("groupid"))
	rows, err := db.Query("SELECT VisitID,Name FROM Visit where GroupID=?",groupid)
	check(err)
	//transform
	columns, err := rows.Columns()
	check(err)
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	if(list=="]"){
		list="[]"
	}

	//debug fmt.Println(list)
	c.Data["json"]=list
	c.ServeJSON()
}

func (c *MainController) HandleSche(){
	fmt.Println("HandleVisit")
	db, err := GetDB()
	check(err)
	if err := c.ParseForm(&s); err != nil {
		//process err
	}
	rows, err := db.Query("SELECT ScheduleID,Name FROM Schedule order by ScheduleID")
	check(err)
	db.Close()
	//Rows转map
	columns, err := rows.Columns()
	check(err)
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	if(list=="]"){
		list="[]"
	}

	_ = rows.Close()
	c.Data["json"]=list
	c.ServeJSON()
}

func (c *MainController) HandleSlot() {
	fmt.Println("_________HandleSlot")
	db, err := GetDB()
	check(err)
	visitid, err := strconv.Atoi(c.GetString("visitid"))
	scheid, err := strconv.Atoi(c.GetString("scheid"))
	slotnum, err := strconv.Atoi(c.GetString("slotnum"))
	page ,err:= strconv.Atoi(c.GetString("page"))
	if (scheid != 0) {
		fmt.Println("_________________________——————————————————————————__进入scheid！=0")
		rows, err := db.Query("SELECT SlotID,CreatedByUserID,CreatedByTimeStamp,SlotType FROM Slot where VisitID=? and ScheduleID=? order by SlotID offset ?*? row fetch next ? row only", visitid, scheid,page,slotnum,page)
		check(err)
		//transform
		columns, err := rows.Columns()
		check(err)
		values := make([]sql.RawBytes, len(columns))
		scanArgs := make([]interface{}, len(values))
		for i := range values {
			scanArgs[i] = &values[i]
		}
		list := "["
		for rows.Next() {
			err = rows.Scan(scanArgs...)
			check(err)
			row := "{"
			var value string
			for i, col := range values {
				if col == nil {
					value = "NULL"
				} else {
					value = string(col)
				}
				columName := strings.ToLower(columns[i])
				cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
				row = row + cell + ","
			}
			row = row[0 : len(row)-1]
			row += "}"
			list = list + row + ","
		}
		list = list[0 : len(list)-1]
		list += "]"
		//当list为空时，前面步骤将消除list：=“[”的结果。
		if(list=="]"){
			list="[]"
		}
		c.Data["json"] = list
		c.ServeJSON()
	} else if (scheid == 0) { //第二种situation，即Schedule没被选择过
		fmt.Println("_________________________——————————————————————————__进入scheid==0")
		//fmt.Println("visitid:"+c.GetString("visitid")+"____page:"+c.GetString("page")+"_____slotnum"+c.GetString("slotnum"))
		rows, err := db.Query("SELECT SlotID,CreatedByUserID,CreatedByTimeStamp,SlotType FROM Slot where VisitID=?  order by SlotID offset ?*? row fetch next ? row only", visitid,page,slotnum,slotnum)
		check(err)
		//transform
		columns, err := rows.Columns()
		check(err)
		values := make([]sql.RawBytes, len(columns))
		scanArgs := make([]interface{}, len(values))
		for i := range values {
			scanArgs[i] = &values[i]
		}
		list := "["
		for rows.Next() {
			err = rows.Scan(scanArgs...)
			check(err)
			row := "{"
			var value string
			for i, col := range values {
				if col == nil {
					value = "NULL"
				} else {
					value = string(col)
				}
				columName := strings.ToLower(columns[i])
				cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
				row = row + cell + ","
			}
			row = row[0 : len(row)-1]
			row += "}"
			list = list + row + ","
		}
		list = list[0 : len(list)-1]
		list += "]"
		//当list为空时，前面步骤将消除list：=“[”的结果。
		if(list=="]"){
			list="[]"
		}
		c.Data["json"] = list
		c.ServeJSON()
	} else{
		fmt.Println("_________________________——————————————————————————__其他情况————————————")
		c.Ctx.WriteString("<html>Error,参数错误，请checkout</html>")
	}
}

//establish the method of use sqlserver proc
func (c *MainController)establishproc1(slotid int,visitID int,scheid int,pagesize int,pageNum int,templateEventIDs int)string{
	var declare=fmt.Sprintf("use TrialOneTraining;declare @p5 dbo.TYPE_INTVALUES;insert into @p5 values(%d);declare @p1 dbo.TYPE_INTVALUES;insert into @p1 values(%d);exec dbo.aaa_ConfirmEvent @visitID=%d,@sampleEvents=1,@otherEvents=1,@scheduleID=%d,@slotIDs=@p5,@templateEventIDs=@p1,@pageNum=%d,@pagesize=%d",slotid,templateEventIDs,visitID,scheid,pageNum,pagesize)
	return declare
}
//handle Event and scan OutBarcode
func (c *MainController)HandleEvent(){
	//visitid, err := strconv.Atoi(c.GetString("visitid"))
	//scheid, err := strconv.Atoi(c.GetString("scheid"))
	//slotid, err := strconv.Atoi(c.GetString("slotid"))
	pagesize, err := strconv.Atoi(c.GetString("eventsize"))
	//fmt.Println(c.GetString("eventsize"))
	pagenum, err := strconv.Atoi(c.GetString("eventNum"))
	visitid:=207
	scheid:=1537
	slotid:=3079
	//pagesize:=10
	//pagenum:=0
	templateEventIDs:=23921
	fmt.Println("_________HandleEvent")
	//fmt.Println(string(c.Ctx.Input.RequestBody))
	db, err := GetDB()
	var procsql=c.establishproc1(slotid,visitid,scheid,pagesize,pagenum,templateEventIDs)
	rows, err := db.Query(procsql)
	check(err)

	columns, err := rows.Columns()
	check(err)
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	//当list为空时，前面步骤将消除list：=“[”的结果。
	if(list=="]"){
		list="[]"
	}
	c.Data["json"] = list
	c.ServeJSON()
}

type StorageOne struct {
	Templateid string
	Instruction string
	Transfer string
	Createstamp string
	Cycle string
	Timepoint string
	Center string
	Insidebarcode string
	Outbarcode string
}
//handle the tableOne Storage
func (c *MainController) StorageOne(){
	fmt.Println("=====================================")
//import the github project scheama will conveninet to storage table data
	//var decoder = schema.NewDecoder()
	var aaa []byte
	aaa=c.Ctx.Input.RequestBody
	var jsonObj []StorageOne
	err:=json.Unmarshal(aaa,&jsonObj)
	check(err)
	db, err := GetDB()
	defer db.Close()
	check(err)
for  i:=0;i<10;i++ {
	Templateid, _ :=strconv.Atoi(jsonObj[i].Templateid)
	Instruction:=jsonObj[i].Instruction
	Transfer, _ :=strconv.Atoi(jsonObj[i].Transfer)
	Cycle:=jsonObj[i].Cycle
	Createstamp:=jsonObj[i].Createstamp
	Timepoint:=jsonObj[i].Timepoint
	Center:=jsonObj[i].Center
	Outbarcode:=jsonObj[i].Outbarcode
	Insidebarcode:=jsonObj[i].Insidebarcode
	var aa=fmt.Sprintf("use TrialOneTraining;insert into aaaEventTemplate_LoadbySlotandSche values(%d,%v,%d,%v,%v,%v,%v,%v,%v)",Templateid,Instruction,Transfer,Cycle,Createstamp,Timepoint,Center,Outbarcode,Insidebarcode)
	fmt.Println(aa)
	_, err := db.Query("use TrialOneTraining;insert into aaaEventTemplate_LoadbySlotandSche values(?,?,?,?,?,?,?,?,?)",Templateid,Instruction,Transfer,Createstamp,Cycle,Timepoint,Center,Outbarcode,Insidebarcode)
	check(err)
}
c.Data["succ"]="succ"
c.ServeJSON()
}


func (c *MainController) StorageTwoAndCompare(){
	fmt.Println("================StorageTwoAndCompare=====================")
	//var decoder = schema.NewDecoder()
	var aaa []byte
	aaa=c.Ctx.Input.RequestBody
	var jsonObj []StorageOne
	err:=json.Unmarshal(aaa,&jsonObj)
	check(err)
	db, err := GetDB()
	defer db.Close()
	var j= len(jsonObj)
	fmt.Println(j)
	check(err)
	for  i:=0;i<j;i++ {
		Templateid, _ :=strconv.Atoi(jsonObj[i].Templateid)
		Instruction:=jsonObj[i].Instruction
		Transfer, _ :=strconv.Atoi(jsonObj[i].Transfer)
		Cycle:=jsonObj[i].Cycle
		Createstamp:=jsonObj[i].Createstamp
		Timepoint:=jsonObj[i].Timepoint
		Center:=jsonObj[i].Center
		Outbarcode:=jsonObj[i].Outbarcode
		Insidebarcode:=jsonObj[i].Insidebarcode
		var aa=fmt.Sprintf("use TrialOneTraining;insert into aaaEventTemplate_LoadbySlotandSche values(%d,%v,%d,%v,%v,%v,%v,%v,%v)",Templateid,Instruction,Transfer,Cycle,Createstamp,Timepoint,Center,Outbarcode,Insidebarcode)
		fmt.Println(aa)
		_, err := db.Query("use TrialOneTraining;insert into aaaEventTemplate_LoadbySlotandScheTwo values(?,?,?,?,?,?,?,?,?)",Templateid,Instruction,Transfer,Createstamp,Cycle,Timepoint,Center,Outbarcode,Insidebarcode)
		check(err)
	}
	fmt.Println("success in one part")
	rows, err := db.Query("select * from  aaaEventTemplate_LoadbySlotandScheTwo  where (select count(1) as num from aaaEventTemplate_LoadbySlotandSche where aaaEventTemplate_LoadbySlotandScheTwo.OutBarcode = aaaEventTemplate_LoadbySlotandSche.OutBarcode\nand aaaEventTemplate_LoadbySlotandScheTwo.Transfer=aaaEventTemplate_LoadbySlotandSche.Transfer) = 0")
	check(err)
	columns, err := rows.Columns()
	check(err)
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	//当list为空时，前面步骤将消除list：=“[”的结果。
	if(list=="]"){
		list="[]"
	}
	if(list=="[]"){
		c.Data["json"]="succ"
		c.ServeJSON()
		fmt.Println("执行发送succ命令")
	}else{
		c.Data["json"]=list
		c.ServeJSON()
		fmt.Println("执行发送list集合命令，其中list为：")
		fmt.Println(list)
	}
}

func (c *MainController) Preparetwo(){
	fmt.Println("Preparetwo")
	db, err := GetDB()
	defer db.Close()
	check(err)
	if err := c.ParseForm(&s); err != nil {
		//process err
	}
	rows, err := db.Query("select TemplateEventSampleTaskID,Instruction,Transfer,CreatedByTimeStamp,Name,TIMEpointCalculation,Center,InsideBarcode,null as 'OutBarcode' from aaaEventTemplate_LoadbySlotandSche where OutBarcode != ''")
	//Rows转map
	columns, err := rows.Columns()
	check(err)
	values := make([]sql.RawBytes, len(columns))
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	list := "["
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		check(err)
		row := "{"
		var value string
		for i, col := range values {
			if col == nil {
				value = "NULL"
			} else {
				value = string(col)
			}
			columName := strings.ToLower(columns[i])
			cell := fmt.Sprintf(`"%v":"%v"`, columName, value)
			row = row + cell + ","
		}
		row = row[0 : len(row)-1]
		row += "}"
		list = list + row + ","
	}
	list = list[0 : len(list)-1]
	list += "]"
	if(list=="]"){
		list="[]"
	}
	_ = rows.Close()
	c.Data["json"]=list
	c.ServeJSON()
}
